1. Field of the Invention
The present invention relates generally to methods and systems for optimizing application entities and customizing database applications at runtime. More particularly, the present invention relates to methods and systems for extending schema objects such as tables in a database without modifying the application source code that accesses the tables.
2. Description of the Related Art
Businesses typically must store a great deal of information regarding their operations, their customers and other entities with which they have developed a relationship. Database applications (online, World Wide Web-based and others) have been developed to assist businesses in tracking, organizing and otherwise making effective use of such information. Each business, however, is unique and may require the storage of information specific to their business. Moreover, businesses are dynamic entities, constantly evolving as their products change and the marketplace in which they operate evolve. Thus, database applications must be individually configured to conform to the unique requirements of the business that deploys the application.
To configure such database applications according to a customer""s specifications conventionally requires that additional columns be added to the relevant database tables to store the requested additional or customer-specific information or attributes. FIG. 1 is a conceptual representation of a schema 110 within a database 100. A schema, such as symbolically represented at 110, is a collection of schema objects. Examples of schema objects include tables and the relationships between the tables, views, sequences, synonyms, clusters, database links, snapshots, procedures, functions and packages. Tables store data in rows and columns and may be thought of as a basic unit of storage in a database. A table may be created by defining a table name and a set of columns. Each column may be given a column name, a datatype and a width (which may be predetermined by the datatype) or a width and scale (for columns of the Number type, for example). A row of a table is a collection of column information corresponding to a single record. FIG. 2 shows an example of such a database table. The illustrative table of FIG. 2 includes 6 columns, each defined to hold a value such as user, street address, city, zip, age and social security number (SSN), for example. The customer deploying an application accessing such a table may wish to store additional information, such as the user""s cell phone number, for example. FIG. 3 shows an example of a table in which a column for a new attribute (in this case the user""s cell phone number) has been added. Adding many such additional columns in this manner, however, may create enormous tables that expand horizontally (as indicated by the arrow in FIG. 3) as additional columns are added to store the additional attributes. In turn, expanding database tables horizontally may cause an inherent column limit to be reached and results in large and sparsely populated tables, as not all attributes that are added are applicable to each row of the table. Such instance specific attributes require a mechanism to be defined to identify the condition or conditions on which the additional attributes are to be enabled. Moreover, adding columns to existing tables to store the desired additional attributes requires that the application source code (which may be written in the JAVA programming language, for example) be modified to access and operate upon the added columns. The modification of the database application may be carried out upon the initial installation and deployment of the application at the business customer site. Alternatively, such application configuration modification may occur periodically, as the business customer""s needs change. Unfortunately, each such application configuration modification may require costly changes to the table structure within the schema and costly changes to the underlying application source code, each of which typically necessitates that the database application be shut down during the update and subsequent recompilation of the database application source code.
Previous efforts at customizing database applications involved the use of a predetermined number of so-called flex (flexible) field columns, as shown at FF1 to FFn in FIG. 4. These pre-installed flex fields are extra columns that are not initially defined when the application is shipped to the customer. These columns may then be configured at the time of installation, to enable the table and the application to address the customer""s specific business needs. However, flex field columns are generally definable only at installation time and not at runtime. Moreover, the number of such pre-created flex fields is necessarily limited. If the scope of the application customization required by the customer causes the number of needed flex fields to exceed the number of available flex fields, extensive and costly modifications may be necessary, as detailed above.
What are needed, therefore, are methods and systems to enable changes to be made to such database applications at runtime, without the need to incur the high costs associated with modifying the database tables and/or the application source code.
It is, therefore, an object of the present invention to provide methods and systems for enabling changes to be made to database applications and application entities such as tables at runtime.
In accordance with the above-described objects and those that will be mentioned and will become apparent below, an embodiment of a method of customizing a database application at runtime, the database application being adapted to access a base table configured to store values associated with a first selected number of base attributes, according to the present invention, includes steps of storing a value associated with a new attribute in an attribute table, the attribute table being configured as a logical extension of the base table and being adapted to store values associated with n new attributes, and storing a definition of the new attribute in an attribute metadata table, the attribute metadata table being adapted to store a definition of each of the n new attributes of the attribute table.
According to further embodiments, each row in the base table maps to a corresponding row in the attribute table. The attribute table may be configured to maintain a number of rows that is equal to a number of rows in the base table. The value storing step may store the value in the attribute table as a selected datatype, such as character (char), for example. The method may further include a step of storing a base-attribute pair in a base-attribute mapping table, the base-attribute pair mapping a name of the base table with the name of the attribute table. A step of storing an extended attribute in an extended attribute table may also be carried out, the extended attribute table including a name column configured to store the extended attribute and a value column configured to store an extended attribute value corresponding to the extended attribute. Rows of the extended value table may be mapped to corresponding rows in the base table. One or more rules may be stored in a rules table, an evaluation of the rule(s) determining whether selected extended attribute values in the extended attribute table are valid. The method may also include the step of storing a logical operator, a first operand, a second operand and a selected extended attribute in the rules table. The application of the logical operator to the first and/or second operands may then determine whether the extended attribute value corresponding to the selected extended attribute is valid. The first operand may include a selected base attribute, the second operand may include a selectable base attribute value. The logical operator may be selected from a group including equal to, less than, less than or equal to, greater than and greater than or equal to, for example. Steps of dividing at least one of the base table, the attribute table, the attribute metadata table, the base-attribute mapping table, the extended attribute table and the rules table into respective non-translatable tables and respective translatable tables may also be carried out. The translatable tables may then be translated into one or more selected languages, as and when needed. Information corresponding to a base attribute, a new attribute, an extended attribute and/or metadata for each accessed row in the base table may be pre-fetched and the pre-fetched information may be stored (cached) in a predetermined area of memory (cache memory).
One or more of the following steps may also be carried out: storing an identification of an application that is allowed to access the value associated with the new attribute in the metadata table; storing a description of the new attribute in the metadata table; storing a default value of the new attribute in the metadata table; storing an enabled/disabled status of the new attribute in the metadata table; storing a required/not required status for the new attribute in the metadata table, and/or storing a datatype of the new attribute in the metadata table.
The present invention is also a database schema, comprising a base table, the base table being configured to store base attribute values associated with a first selected number of base attributes; an attribute table, the attribute table being configured as a logical extension of the base table and being adapted to store new attribute values associated with new attributes, and an attribute metadata table, the attribute metadata table being adapted to store a definition of each of the n new attributes of the attribute table.
The present invention may also be viewed as a computer system, comprising at least one processor; at least one data storage device; a plurality of processes spawned by said at least one processor, the processes including processing logic for customizing a database application at runtime, the database application being adapted to access a base table configured to store values associated with a first selected number of base attributes, the processes carrying out the steps of: storing a value associated with a new attribute in an attribute table, the attribute table being configured as a logical extension of the base table and being adapted to store values associated with n new attributes; storing a definition of the new attribute in an attribute metadata table, the attribute metadata table being adapted to store a definition of each of the n new attributes of the attribute table.